package com.xpec.c4.db.hibernate.manager;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.hibernate.impl.SessionFactoryImpl;

/**
 * 存取資料庫資料的工具
 * 
 * @author miriamchen
 * 
 */
public class HibernateDAOUtil {
	/** log ibject */
	private static final Log log = LogFactory.getLog(HibernateDAOUtil.class);

	private ManagerType managerType;
	private static HibernateDAOUtil gameIns = new HibernateDAOUtil(ManagerType.GAME);
	private static HibernateDAOUtil logIns = new HibernateDAOUtil(ManagerType.LOG);
	private static HibernateDAOUtil businessIns = new HibernateDAOUtil(ManagerType.BUSINESS);
	private static HibernateDAOUtil daoInstances[] = { gameIns, logIns, businessIns };
	private String databaseName = null;

	public static HibernateDAOUtil getGameDAO() {
		return gameIns;
	}

	public static HibernateDAOUtil getLogDAO() {
		return logIns;
	}

	public static HibernateDAOUtil getBusinessDAO() {
		return businessIns;
	}

	private HibernateDAOUtil(ManagerType managerType) {
		this.managerType = managerType;
	}

	public Session getSession() {
		switch (managerType) {
		case GAME:
			return HibernateManager.getSession();
		case LOG:
			return HibernateLogManager.getSession();
		case BUSINESS:
			return HibernateBusinessManager.getSession();
		default:
			if (log.isErrorEnabled()) {
				log.error("getSession() unknow case " + managerType.toString());
			}
			break;
		}
		return null;
	}

	public Serializable save(Object obj) {
		return getSession().save(obj);
	}

	public void saveOrUpdate(Object obj) {
		getSession().saveOrUpdate(obj);
	}

	@SuppressWarnings("unchecked")
	public <T> T findById(Class<T> clazz, Serializable id) {
		return (T) getSession().get(clazz, id);
	}

	public void update(Object object) {
		getSession().update(object);
	}

	public void delete(Object object) {
		getSession().delete(object);
	}

	public void closeSession() {
		switch (managerType) {
		case GAME:
			HibernateManager.closeSession();
			break;
		case LOG:
			HibernateLogManager.closeSession();
			break;
		case BUSINESS:
			HibernateBusinessManager.closeSession();
			break;
		default:
			if (log.isErrorEnabled()) {
				log.error("closeSession() unknow case " + managerType.toString());
			}
			break;
		}
	}

	public void beginTransaction() {
		getSession().beginTransaction();
	}

	public void commit() {
		Transaction t = getSession().getTransaction();
		if (t != null && t.isActive()) {
			t.commit();
		}
	}

	public void rollback() {
		Transaction t = getSession().getTransaction();
		if (t != null && t.isActive()) {
			t.rollback();
		}
	}

	public Criteria createCriteria(Class<?> clazz) {
		return getSession().createCriteria(clazz);
	}

	@SuppressWarnings("unchecked")
	public <T> List<T> _find(Class<T> clazz, Criteria crit, Order[] defaultOrder, int start, int max) {
		List<T> list = null;
		if (crit == null)
			crit = createCriteria(clazz);
		if (defaultOrder != null) {
			for (Order order : defaultOrder) {
				crit.addOrder(order);
			}
		}

		if (max == 0 && start == 0) {
			// do not set limit
		} else {
			if (start > 0) {
				crit.setFirstResult(start);
			}
			if (max > 0) {
				crit.setMaxResults(max);
			}
		}
		list = (List<T>) crit.list();
		return list;
	}

	/**
	 * 從資料表中只取指定的一個欄位出來
	 * 
	 * @param modelClazz
	 *            代表資料表的 model class
	 * @param columnName
	 *            指定欄位的名稱
	 * @param columnClass
	 *            指定欄位的資料型態 class
	 * @param crit
	 * @param defaultOrder
	 * @param start
	 * @param max
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public <T> List<T> findSingleColumn(Class<?> modelClazz, String columnName, Class<T> columnClass,
			Criteria crit, Order[] defaultOrder, int start, int max) {
		List<T> list = null;
		if (crit == null)
			crit = createCriteria(modelClazz);
		if (defaultOrder != null) {
			for (Order order : defaultOrder) {
				crit.addOrder(order);
			}
		}

		crit.setProjection(Projections.projectionList().add(Projections.property(columnName)));

		if (max == 0 && start == 0) {
			// do not set limit
		} else {
			if (start > 0) {
				crit.setFirstResult(start);
			}
			if (max > 0) {
				crit.setMaxResults(max);
			}
		}
		list = (List<T>) crit.list();
		return list;
	}

	public <T> List<T> find(Class<T> clazz, Criteria crit, Order defaultOrder) {
		return _find(clazz, crit, defaultOrder == null ? null : new Order[] { defaultOrder }, 0, 0);
	}

	/**
	 * 取得指定條件搜尋出的資料筆數
	 * 
	 * @param modelClazz
	 *            代表資料表的 model class
	 * @param crit
	 *            搜尋條件
	 * @return 結果資料筆數
	 */
	public int getRowCount(Class<?> modelClazz, Criteria crit) {
		if (crit == null) {
			crit = createCriteria(modelClazz);
		}

		Number res = (Number) crit.setProjection(Projections.rowCount()).uniqueResult();

		return res.intValue();
	}

	@SuppressWarnings("unchecked")
	public <T> T findUnique(Class<T> clazz, Criteria crit) {
		Object res = crit.uniqueResult();
		return (T) res;
	}

	public <T> T findFirst(Class<T> clazz, Criteria crit, Order defaultOrder) {
		List<T> list = find(clazz, crit, defaultOrder);
		if (list != null && list.size() > 0) {
			return list.get(0);
		} else {
			return null;
		}
	}

	public <T> ArrayList<T> find(Class<T> clazz, Criteria crit, Order defaultOrder, int start, int max) {
		List<T> list = _find(clazz, crit, defaultOrder == null ? null : new Order[] { defaultOrder }, start, max);
		return new ArrayList<T>(list);
	}

	@SuppressWarnings("unchecked")
	public <T> List<T> queryHql(Class<T> clazz, String hql, Map<String, Object> paramMap, int first, int max) {
		Query qry = getSession().createQuery(hql);
		setParameters4Qry(qry, paramMap);
		if (first > 0)
			qry.setFirstResult(first);
		if (max > 0)
			qry.setMaxResults(max);
		return (List<T>) qry.list();
	}

	private static void setParameters4Qry(Query qry, Map<String, Object> paramMap) {
		Iterator<String> it = null;
		if (paramMap != null) {
			it = paramMap.keySet().iterator();
			while (it.hasNext()) {
				String key = it.next();
				if (key == null || key.equals(""))
					continue;
				qry.setParameter(key, paramMap.get(key));
			}
		}
	}

	// public int executeHql(String hql, Map<String, Object> paramMap) {
	// Query qry = getSession().createQuery(hql);
	// setParameters4Qry(qry, paramMap);
	// return qry.executeUpdate();
	// }
	//
	public int executeHql(String hql, String paramKey, Object paramVal) {
		Map<String, Object> params = new HashMap<String, Object>();
		params.put(paramKey, paramVal);
		return this.executeHql(hql, params);
	}

	public int executeHql(String hql, Map<String, Object> params) {
		Query qry = getSession().createQuery(hql);
		if (params != null) {
			for (Map.Entry<String, Object> entry : params.entrySet()) {
				String key = entry.getKey();
				if (key == null || key.equals(""))
					continue;
				qry.setParameter(key, entry.getValue());
			}
		}
		return qry.executeUpdate();
	}

	/**
	 * 用hibernate的SQLQuery有時會有問題.例如sql裡有:符號時,hibernate會以為要輸入參數而報錯.
	 * 但那是mysql中的變數指定方法.並非用來給hibernate參數
	 * 此時只能用native connection執行
	 * 
	 * @param sqls
	 * @param commit
	 * @throws Exception
	 */
	public static void executeSqlsWithNativeConnection(String[] sqls, boolean commit, boolean ignoreError,
			Connection connection) throws Exception {
		for (String sql : sqls) {
			if (sql == null || sql.equals(""))
				continue;
			try {
				PreparedStatement psStmt = connection.prepareStatement(sql);
				psStmt.execute();
			} catch (Exception e) {
				if (ignoreError) {
					e.printStackTrace();
					continue;
				} else {
					throw e;
				}
			}
		}
		if (commit) {
			connection.commit();
		}
	}

	public boolean executeSqls(String[] sqls, boolean commit, Map<String, Object> paramMap) {
		boolean updated = false;
		if (commit) {
			this.beginTransaction();
		}
		Session session = this.getSession();
		for (String sql : sqls) {
			if (sql == null || sql.equals(""))
				continue;
			SQLQuery qry = session.createSQLQuery(sql);
			if (paramMap != null) {
				for (Map.Entry<String, Object> param : paramMap.entrySet()) {
					qry.setParameter(param.getKey(), param.getValue());
				}
			}
			updated = qry.executeUpdate() > 0 || updated;
		}
		if (commit) {
			this.commit();
		}
		return updated;
	}

	public boolean executeSql(String sql, boolean commit) {
		return executeSqls(new String[] { sql }, commit, null);
	}

	public boolean executeSqls(String[] sqls, boolean commit) {
		return executeSqls(sqls, commit, null);
	}

	public boolean executeSql(String sql, boolean commit, Map<String, Object> paramMap) {
		return executeSqls(new String[] { sql }, commit, paramMap);
	}

	public static void closeAllSessions() {
		for (HibernateDAOUtil instance : daoInstances) {
			instance.closeSession();
		}
	}

	public static void rollbackAll() {
		for (HibernateDAOUtil instance : daoInstances) {
			instance.rollback();
			instance.closeSession();
		}
	}

	public static void commitAll() {
		for (HibernateDAOUtil instance : daoInstances) {
			instance.commit();
		}
	}

	public String getDatabaseName(boolean closeSessionIfOpened) {
		if (this.databaseName == null) {
			SessionFactoryImpl impl = (SessionFactoryImpl) (this.getSession().getSessionFactory());
			try {
				Connection connection = impl.getConnectionProvider().getConnection();
				databaseName = connection.getCatalog();
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				if (closeSessionIfOpened) {
					this.getSession().close();
				}
			}
		}
		return databaseName;
	}

	public static void initAllConfigurations() {
		for (HibernateDAOUtil instance : daoInstances) {
			instance.getSession();
		}
		closeAllSessions();
	}

	/**
	 * 用這方法來取得資料庫中最更新的model物件,這是為了避免session cache而取得舊的資料
	 * 先用evict清理session的cache,然後查詢
	 * 
	 * @param modelClazz
	 * @param id
	 * @param closeSession
	 *            查詢完是否要關掉session
	 * @return
	 */
	public <T> T evictAndGetModel(Class<T> modelClazz, Serializable id, boolean closeSession) {
		try {
			T result = this.findById(modelClazz, id);
			if (result == null)
				return null;
			this.getSession().evict(result);
			result = this.findById(modelClazz, id);
			return result;
		} catch (HibernateException e) {
			throw e;
		} finally {
			if (closeSession) {
				this.closeSession();
			}
		}
	}
}
